package com.future.dao;

import java.sql.Connection;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.future.model.po.AreaModel;

public class AreaDAO extends BaseDAO<AreaModel> {

	private static Logger logger = LogManager.getLogger(AreaDAO.class);

	public AreaDAO() {

	}

	public AreaDAO(Connection conn) {
		super(conn);
	}

	public boolean save(AreaModel area) {
		String sql = "insert into area (area , code ) values (?,?)";
		return this.update(sql, area.getArea(), area.getCode());
	}

	public List<AreaModel> findAll() {
		String sql = "select * from area ";
		return this.searchAsBeanList(sql, AreaModel.class);
	}

	public List<AreaModel> findByCode(String code) {
		AreaModel area = new AreaModel();
		area.setCode(code);
		return findByCode(area);
	}

	public List<AreaModel> findByCode(AreaModel area) {
		String sql = "select * from area where code = ?";
		return this.searchAsBeanList(sql, AreaModel.class, area.getCode());
	}

	public AreaModel findById(Integer id) {
		AreaModel area = new AreaModel();
		area.setId(id);
		return findById(area);
	}

	public AreaModel findById(AreaModel area) {
		String sql = "select * from area where id = ?";
		List<AreaModel> list = this.searchAsBeanList(sql, AreaModel.class, area.getId());
		return list.get(0);
	}

	/**
	 * this method use mysql regexp expression , like '11[0-9][0-9][0]{5}'
	 * @param code
	 * @return
	 */
	public List<AreaModel> fuzzyByCode(String code) {
		String sql = "select * from area where code regexp ?";
		logger.debug(sql);
		return this.searchAsBeanList(sql, AreaModel.class, code);
	}
	public List<AreaModel> fuzzyByCode(String code,String areacode) {
		//String sql = "select * from area where code regexp ?";
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.id,a.area,a.code,a.fullname, ")
		.append(" (select count(deparIsOrnot) from area where code like (CASE WHEN LOCATE('0000000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,2),'%')")
		.append(" WHEN LOCATE('00000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,4),'%')")
		.append(" WHEN LOCATE('000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,6),'%')")
		.append(" ELSE a.code END)  and deparIsOrnot=1) deparSum,")
		.append(" (select count(townIsOrnot)  from area where code like (CASE WHEN LOCATE('0000000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,2),'%')")
		.append(" WHEN LOCATE('00000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,4),'%')")
		.append(" WHEN LOCATE('000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,6),'%') ")
		.append(" ELSE a.code END) and townIsOrnot=1) townSum,")
		.append(" (select count(surveyIsOrnot) from area where code like (CASE WHEN LOCATE('0000000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,2),'%')")
		.append(" WHEN LOCATE('00000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,4),'%')")
		.append(" WHEN LOCATE('000',a.code)>0 THEN CONCAT(SUBSTR(a.code,1,6),'%')")
		.append(" ELSE a.code END) and surveyIsOrnot=1) surveySum from area a")
		.append(" where a.code regexp 	?");
		logger.debug(sql.toString());
		return this.searchAsBeanList(sql.toString(), AreaModel.class, code);
	}
	
	public boolean updateAreaByCode(String code){
		String temp = code.substring(0,9);
		String flag =code.substring(9,11);
		String sql = null;
		if(("01").equals(flag)){
			sql = "update area set surveyIsOrnot=? where code =?";
		}else if(("34").equals(flag)||("35").equals(flag)){
			sql = "update area set townIsOrnot=? where code =?";
		}else{
			sql = "update area set deparIsOrnot=? where code =?";
		}
		logger.info("修改areasql="+sql);
		return this.update(sql, 1,temp);
	}
}
